Imports System.Data
Imports System.Data.SqlClient
Imports DevExpress.XtraExport
Imports System.Text
Imports DevExpress.XtraGrid.Export
Imports System.Threading
Imports System.Globalization

Public Class AllTransaction

    Private Filename As String = "C:\Program Files\Common Files\MemberSystem\AllTransaction.xml"
    Dim DateTime_From As Date
    Dim DateTime_To As Date

    Private Sub mnuSaveLayout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSaveLayout.Click
        Me.GridControl1.MainView.SaveLayoutToXml(Filename)
    End Sub

    Private Sub mnuLoadDefault_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuLoadDefault.Click
        Dim File, Folder, FileCollection
        'Dim fso As New Scripting.FileSystemObject

        If MsgBox("Are You Sure Want to Load Default Layout!", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then

            If IO.File.Exists(Filename) Then
                IO.File.Delete(Filename)
            End If

            MsgBox("Please Reload the Page as Default Layout Loaded", MsgBoxStyle.Information)
            Me.Close()

        End If

    End Sub

    Private Sub GridControl1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles GridControl1.MouseDown
        If e.Button = MouseButtons.Right Then
            Me.ContextMenu = ContextMenu1
            Exit Sub
        End If
    End Sub

    Private Sub AllTransaction_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadIcon(Me)
        Me.lblTotalRecords.Text = ""
        loadLayout(Me.GridControl1, Filename)
        ' RefreshGrid()
        RefreshGridPivot()
    End Sub

    Private Sub RefreshGrid()
        Dim str As String = "SELECT * FROM TransactionView"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds, "Records")
        Me.GridControl1.DataSource = ds.Tables(0)
        Me.lblTotalRecords.Text = ds.Tables(0).Rows.Count & " Record(s)"
    End Sub

    Private Sub RefreshGridPivot()
        Dim str As String = "SELECT * FROM TransactionView"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds, "Records")
        Me.PivotGridControl1.DataSource = ds.Tables(0)
        'Me.lblTotalRecords.Text = ds.Tables(0).Rows.Count & " Record(s)"
    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Dim saveDialog As New SaveFileDialog
        saveDialog.Title = "Export To "
        saveDialog.Filter = "Microsoft Excel|*.xls|HTML Documents|*html|Text Files|*.txt|XML Document|*.xml"
        If saveDialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Try
                Select Case saveDialog.FilterIndex
                    Case 1
                        Call ExportTo(New ExportXlsProvider(saveDialog.FileName), GridView1)
                    Case 2
                        Call ExportTo(New ExportHtmlProvider(saveDialog.FileName), GridView1)
                    Case 3
                        Call ExportTo(New ExportTxtProvider(saveDialog.FileName), GridView1)
                    Case 4
                        Call ExportTo(New ExportXmlProvider(saveDialog.FileName), GridView1)
                End Select
                MsgBox("Successfully Retrive to " & saveDialog.FileName & ".", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox("Fail To Retrive : " & ex.Message, MsgBoxStyle.Information)
            End Try

        End If
    End Sub

    Private Sub ExportTo(ByVal provider As DevExpress.XtraExport.IExportProvider, ByVal grid As DevExpress.XtraGrid.Views.Grid.GridView)
        Dim link As BaseExportLink = grid.CreateExportLink(provider)
        CType(link, GridViewExportLink).ExpandAll = False
        link.ExportTo(True)
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

    Private Sub btnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReport.Click
        Dim frm As New Report_AllRecords
        frm.cboDateFrom.EditValue = DateTime_From
        frm.cboDateTo.EditValue = DateTime_To
        frm.ShowDialog()

    End Sub


    Private Sub rbFilterBy_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFilterBy.SelectedIndexChanged
        Dim str As String
        Dim cmd As New SqlCommand("", gCnnPCE)

        Select Case rbFilterBy.EditValue
            Case 0 ' Daily
                str = "SELECT * FROM TransactionView WHERE TTDate > @DateFrom AND TTDate < @DateTo"
                cmd.CommandText = str
                Dim Today_DateTime_From As New Date(Now.Year, Now.Month, Now.Day, 0, 0, 0)
                Dim Today_DateTime_To As New Date(Now.Year, Now.Month, Now.Day, 23, 59, 59)
                cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = Today_DateTime_From
                cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = Today_DateTime_To

            Case 1 ' Weekly
                str = "SELECT * FROM TransactionView WHERE TTDate > @DateFrom AND TTDate < @DateTo"
                cmd.CommandText = str
            
                Select Case Now.DayOfWeek
                    Case 1
                        DateTime_From = New Date
                        DateTime_From = Now.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(6)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 2
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-1)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(5)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 3
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-2)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(4)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 4
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-3)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(3)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 5
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-4)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(2)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 6
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-5)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddDays(1)
                        DateTime_To = DateTime_To.AddHours(12)
                    Case 0 'Sunday
                        DateTime_From = New Date
                        DateTime_From = Now.AddDays(-6)
                        DateTime_From = DateTime_From.AddHours(-12)
                        DateTime_To = New Date
                        DateTime_To = Now.AddHours(12)
                        ' DateTime_To = DateTime_To.AddHours(12)
                End Select
                cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To

            Case 2 'Monthly

                str = "SELECT * FROM TransactionView WHERE TTDate > @DateFrom AND TTDate < @DateTo"
                cmd.CommandText = str
                Dim DateTime_From As New Date(Now.Year, Now.Month, 1, 0, 0, 0)
                Select Case Now.Month
                    Case 1, 3, 5, 7, 8, 10, 12
                        Dim DateTime_To As New Date(Now.Year, Now.Month, 31, 23, 59, 59)
                        cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                        cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To
                    Case 2
                        Dim DateTime_To As New Date(Now.Year, Now.Month, 28, 23, 59, 59)
                        cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                        cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To
                    Case 4, 6, 9, 11
                        Dim DateTime_To As New Date(Now.Year, Now.Month, 30, 23, 59, 59)
                        cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                        cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To
                End Select



            Case 3 'Yearly
                str = "SELECT * FROM TransactionView WHERE TTDate > @DateFrom AND TTDate < @DateTo"
                cmd.CommandText = str
                Dim DateTime_From As New Date(Now.Year, 1, 1, 0, 0, 0)
                Dim DateTime_To As New Date(Now.Year, 12, 31, 23, 59, 59)
                cmd.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = DateTime_From
                cmd.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = DateTime_To

            Case 4 'All
                str = "SELECT * FROM TransactionView "
                cmd.CommandText = str
        End Select

        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds, "Records")
        Me.GridControl1.DataSource = ds.Tables("Records")
        Me.lblTotalRecords.Text = ds.Tables(0).Rows.Count & " Record(s)"

    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If GridView1.RowCount = 0 Then
            Exit Sub
        End If
        Dim i As Integer = ArrayToString(GridView1.GetSelectedRows)
        Dim SelectedTTNo As String = GridView1.GetRowCellValue(i, GridView1.Columns("TTNo"))

        Dim frm As New New_Transaction
        frm.Action = "EDIT"
        frm.TTNo = SelectedTTNo
        frm.Show()

    End Sub
End Class